package com.fxd.jdbctemplate;

import com.fxd.domain.account;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @auther fuxudong
 * @date 2021/1/28 - 11:31
 * jdbcTemplate的crud操作
 */
public class jdbctemplatespring {

    public static void main(String[] args) {

        //1，获取容器
        ClassPathXmlApplicationContext ca = new ClassPathXmlApplicationContext("bean.xml");
        JdbcTemplate jt = ca.getBean("jdbctemplate", JdbcTemplate.class);
        //jt.execute("insert into account(name,money) values ('ff',1000)");

        //保存
        //jt.update("insert into account(name,money)values (?,?)","ddd",55f);
        //更新
        //jt.update("update account set name=?,money=? where id=?","ddd",44f,3);
        //删除
        //jt.update("delete from account where id=?",3);
        //查询所有
        List<account> accounts = jt.query("select * from account where money > ?", new accountRowMapper(), 100f);
        List<account> as = jt.query("select * from account where money > ?", new BeanPropertyRowMapper<account>(account.class), 100f);
        for(account a:as){
            System.out.println(a);
        }
        //查询一个
        List<account> acc = jt.query("select * from account where id = ?", new BeanPropertyRowMapper<account>(account.class), 1);
        System.out.println(acc.isEmpty()? "没有内容": accounts.get(0));
        //查询返回一行一列（使用聚合函数，但不加group by子句)
        Long lon = jt.queryForObject("select count(money) from account where money > ?", Long.class, 100f);
        System.out.println(lon);

    }
}


/**
 * 定义account的封装策略
 */
class accountRowMapper implements RowMapper<account>{
    /**
     * 把结果集中的数据封装到account中，然后又spring把每个account添加到集合中
     * @param resultSet
     * @param i
     * @return
     * @throws SQLException
     */
    public account mapRow(ResultSet resultSet, int i) throws SQLException {
        account account = new account();
        account.setId(resultSet.getInt("id"));
        account.setName(resultSet.getString("name"));
        account.setMoney(resultSet.getFloat("money"));
        return account;
    }
}

